﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;

namespace Common.Tools
{
    public class ExcelHelper
    {
        public void CreateExcel(HttpResponse resp, DataSet ds, string typeid, string FileName)
        {
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312 ");
            resp.AppendHeader("Content-Disposition ", "attachment;filename= " + FileName);
            string colHeaders = " ", ls_item = " ";
            int i = 0;

            //定义表对象与行对像，同时用DataSet对其值进行初始化 
            DataTable dt = ds.Tables[0];
            DataRow[] myRow = dt.Select(" ");
            // typeid== "1 "时导出为EXCEL格式文件；typeid== "2 "时导出为XML格式文件 
            if (typeid == "1 ")
            {
                //取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符 
                for (i = 0; i < dt.Columns.Count - 1; i++)
                    colHeaders += dt.Columns[i].Caption.ToString() + "\t ";
                colHeaders += dt.Columns[i].Caption.ToString() + "\n ";
                //向HTTP输出流中写入取得的数据信息 
                resp.Write(colHeaders);
                //逐行处理数据 
                foreach (DataRow row in myRow)
                {
                    //在当前行中，逐列获得数据，数据之间以\t分割，结束时加回车符\n 
                    for (i = 0; i < row.Table.Columns.Count - 1; i++)
                        ls_item += row[i].ToString() + "\t ";
                    ls_item += row[i].ToString() + "\n ";
                    //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据 
                    resp.Write(ls_item);
                    ls_item = " ";
                }
            }
            else
            {
                if (typeid == "2 ")
                {
                    //从DataSet中直接导出XML数据并且写到HTTP输出流中 
                    resp.Write(ds.GetXml());
                }
            }
            //写缓冲区中的数据到HTTP头文件中 
            resp.End();
        }

        /// <summary>
        /// 导出文件.csv
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="rowNameList"></param>
        /// <param name="strFileName"></param>
        public bool ExportExcel(DataTable dtSource, Dictionary<string, string> rowNameList, string strFileName)
        {
            if (dtSource == null) return false;

            try
            {
                //var workbook = new HSSFWorkbook();
                //var sheet = workbook.CreateSheet();

                ////填充表头  
                //var dataRow = sheet.CreateRow(0);
                //foreach (DataColumn column in dtSource.Columns)
                //{
                //    var columnName = rowNameList.ContainsKey(column.ColumnName)
                //        ? rowNameList[column.ColumnName]
                //        : column.ColumnName;
                //    dataRow.CreateCell(column.Ordinal).SetCellValue(columnName);
                //}


                ////填充内容  
                //for (int i = 0; i < dtSource.Rows.Count; i++)
                //{
                //    dataRow = sheet.CreateRow(i + 1);

                //    for (int j = 0; j < dtSource.Columns.Count; j++)
                //    {
                //        dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());
                //    }
                //}


                ////保存  
                //using (var ms = new MemoryStream())
                //{
                //    using (var fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                //    {
                //        workbook.Write(fs);
                //        fs.Close();
                //    }
                //}
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

    }
}
